/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bussiness;

import Connection.DBUtil;
import Connection.Employee;
import Connection.Transfer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author SONY
 */
public class TransferDAO {
    
    private static Connection conn = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;
    private static Transfer objTransfer = new Transfer();
    private static DBUtil db = new DBUtil();
    private static Vector transfer;
    
    public static void insertTranfer(Transfer objTransfer1) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spInsertTranfer(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
        cs.setString(1, objTransfer1.getTransferID());
        cs.setString(2, objTransfer1.getEmployeeID());
        cs.setString(3, objTransfer1.getFromProjectID());
        cs.setString(4, objTransfer1.getToProjectID());
        cs.setString(5, objTransfer1.getFromDepartmentID());
        cs.setString(6, objTransfer1.getToDepartmentID());
        cs.setString(7, objTransfer1.getFromLocationID());
        cs.setString(8, objTransfer1.getToLocationID());
        cs.setDate(9, new java.sql.Date(objTransfer1.getJoinDate().getTime()));
        cs.setDate(10, new java.sql.Date(objTransfer1.getEndDate().getTime()));
        cs.setBoolean(11, objTransfer1.getStatus());
        cs.executeUpdate();
        conn.close();
    }
    
    public static void updateTransfer(String transferID, Transfer objTranferUpdate) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spUpDateTransfer(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
        cs.setString(1, transferID);
        cs.setString(2, objTranferUpdate.getEmployeeID());
        cs.setString(3, objTranferUpdate.getFromProjectID());
        cs.setString(4, objTranferUpdate.getToProjectID());
        cs.setString(5, objTranferUpdate.getFromDepartmentID());
        cs.setString(6, objTranferUpdate.getToDepartmentID());
        cs.setString(7, objTranferUpdate.getFromLocationID());
        cs.setString(8, objTranferUpdate.getToLocationID());
        cs.setDate(9, new java.sql.Date(objTranferUpdate.getJoinDate().getTime()));
        cs.setDate(10, new java.sql.Date(objTranferUpdate.getEndDate().getTime()));        
        cs.setBoolean(11, objTranferUpdate.getStatus());
        cs.executeUpdate();
        conn.close();
    }
    
    public static void deleteTransfer(String transferID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spDeleteTransfer(?)}");
        cs.setString(1, transferID);
        cs.executeUpdate();
        conn.close();
    }
    
    public static int countTotalTransfer() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spTotalTransfer}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countTotalTransferApprove() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spTotalStatus}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countTotalTranferNotApprove() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spTotalNotStatus}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static Transfer searchTransferByID(String transferID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferID(?)}");
        cs.setString(1, transferID);
        rs = cs.executeQuery();
        while (rs.next()) {            
            objTransfer.setStt(rs.getInt(1));
            objTransfer.setTransferID(rs.getString(2));
            objTransfer.setEmployeeID(rs.getString(3));
            objTransfer.setFromProjectID(rs.getString(4));
            objTransfer.setToProjectID(rs.getString(5));
            objTransfer.setFromDepartmentID(rs.getString(6));
            objTransfer.setToDepartmentID(rs.getString(7));
            objTransfer.setFromLocationID(rs.getString(8));
            objTransfer.setToLocationID(rs.getString(9));
            objTransfer.setJoinDate(rs.getDate(10));
            objTransfer.setEndDate(rs.getDate(11));
            objTransfer.setStatus(rs.getBoolean(12));
        }
        return objTransfer;
    }
    
    public static int countSearchTransferByEmployeeID(String EmployeeID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByEmployeeID(?)}");
        cs.setString(1, EmployeeID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByFromLocationID(String locationID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByFromLocationID(?)}");
        cs.setString(1, locationID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByFromDepartmentID(String departmentID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByFromDepartmentID(?)}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByFromProjectID(String projectID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByFromProjectID(?)}");
        cs.setString(1, projectID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByToProjectID(String projectID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByToProjectID(?)}");
        cs.setString(1, projectID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByToLocationID(String locationID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByToLocationID(?)}");
        cs.setString(1, locationID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchTransferByToDepartmentID(String departmentID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchTransferByToDepartmentID(?)}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
}
